﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using MySql.Data.MySqlClient;
using System.Data;
using VSDBM.Code.dto;

namespace VSDBM.Code.dao
{
    public class MessageDAO
    {

        public static String sendMessage(Message message)
        {
            try
            {
                MySqlConnection connection = Connection.getConnection();
                MySqlCommand query = new MySqlCommand("INSERT INTO messages (touserid,senduserid,title,message,date,status) VALUES (" + message.touserid + "," + message.senduserid + ",'" + message.title + "','" + message.message + "','" + message.date + "',1)", connection);
                query.ExecuteNonQuery();
                return "true";
            }
            catch (Exception e)
            {
                return "ERRO! - " + e.Message;
            }

        }

        public static String delete(Message message)
        {
            try
            {
                MySqlConnection connection = Connection.getConnection();
                MySqlCommand query = new MySqlCommand("DELETE FROM messages WHERE id = " + message.id + "", connection);
                query.ExecuteNonQuery();
                return "true";
            }
            catch (Exception e)
            {
                return e.Message;
            }
        }

        public static String deleteid(int id)
        {
            try
            {
                MySqlConnection connection = Connection.getConnection();
                MySqlCommand query = new MySqlCommand("DELETE FROM messages WHERE id = " + id + "", connection);
                query.ExecuteNonQuery();
                return "true";
            }
            catch (Exception e)
            {
                return e.Message;
            }
        }

        public static DataSet select( Boolean campsjoin, String filters, String orderby)
        {

            DataSet dSet = new DataSet();
            try
            {
                MySqlConnection connection = Connection.getConnection();
                MySqlDataAdapter adapter = new MySqlDataAdapter();

                if (campsjoin)
                {
                    VSDBM.Code.dto.User usuariologado = (VSDBM.Code.dto.User)HttpContext.Current.Session["usuariologado"];
                    adapter.SelectCommand = new MySqlCommand("SELECT  messages.id, users.login , messages.title, messages.date, messages.status FROM messages JOIN users ON (users.id = messages.touserid) WHERE touserid = " + usuariologado.id + "" + filters + " " + orderby, connection);
                } 
                else
                {
                    adapter.SelectCommand = new MySqlCommand("SELECT  * FROM messages " + filters + " " + orderby, connection);
                }
                adapter.Fill(dSet);
                return dSet;
            }
            catch (Exception e)
            {
                return dSet;
            }
        }

        public static List<Message> DStoList(DataSet ds)
        {
            List<Message> list = new List<Message>();
            try
            {
                for (int i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
                {
                    DataTable registro = ds.Tables[0];
                    list.Add(new Message(int.Parse(registro.Rows[i]["id"].ToString()), int.Parse(registro.Rows[i]["touserid"].ToString()), int.Parse(registro.Rows[i]["senduserid"].ToString()), registro.Rows[i]["title"].ToString(), registro.Rows[i]["message"].ToString(), registro.Rows[i]["date"].ToString(), int.Parse(registro.Rows[i]["status"].ToString())));
                }

            }
            catch (Exception e) { }
            return list;
        }

    }
}